﻿USE master
GO

IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'ManagerPerson') DROP DATABASE ManagerPerson
GO

CREATE DATABASE ManagerPerson
GO

USE ManagerPerson
GO






-- Profile------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Profiles') DROP TABLE Profiles
GO
CREATE TABLE Profiles
(
	ProID							INT IDENTITY(1, 1) PRIMARY KEY,
	FirstName						NVARCHAR(50) NOT NULL,
	LastName						NVARCHAR(50) NOT NULL,
	Birthday						DATETIME NOT NULL,
	Gender							BIT NOT NULL,
	Height							FLOAT NULL DEFAULT 0,
	[Address]						NVARCHAR(100) NULL,
	Country							NVARCHAR(100) NULL,
	HomePhone						VARCHAR(20) NULL,
	WorkPhone						VARCHAR(20) NULL,
	MaritalStatus					NVARCHAR(50) NOT NULL,
	Religion						NVARCHAR(100) NULL,
	Caste							NVARCHAR(100) NULL
)
GO



-- Users --------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Users') DROP TABLE Users
GO
CREATE TABLE Users
(
	UserID							INT IDENTITY(1, 1) PRIMARY KEY,
	Email							VARCHAR(50) UNIQUE NOT NULL,
	[Password]						VARCHAR(50) NOT NULL,
	CreateDate						DATETIME NULL DEFAULT GETDATE(),
	LastLoginDate					DATETIME NULL DEFAULT GETDATE(),
	Views							INT NULL DEFAULT 0,
	IsActive						BIT NOT NULL DEFAULT 1,
	ProID							INT NOT NULL,
	CONSTRAINT FK_Users_Profiles_ID FOREIGN KEY (ProID) REFERENCES Profiles(ProID)
)
GO

-- Admin--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Adminstrators') DROP TABLE Adminstrators
GO
CREATE TABLE Adminstrators
(
	AdminID							INT IDENTITY(1, 1) PRIMARY KEY,
	Username						VARCHAR(50) UNIQUE NOT NULL,
	[Password]						VARCHAR(50) NOT NULL,
	RoleNo							VARCHAR(50) NOT NULL DEFAULT 'Manager',
	ProID							INT NULL,
	CONSTRAINT FK_Admins_Profiles_ID FOREIGN KEY (ProID) REFERENCES Profiles(ProID)
)
GO

-- Photos --------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Photos') DROP TABLE Photos
GO
CREATE TABLE Photos
(
	PhotoID							INT IDENTITY(1, 1) PRIMARY KEY,
	Name							VARCHAR(100) NULL,
	IsAvata							BIT NOT NULL,
	UserID							INT NOT NULL,
	CONSTRAINT FK_Users_Photos_ID FOREIGN KEY (UserID) REFERENCES Users(UserID)
)
GO





-- Messages --------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Messages') DROP TABLE Messages
GO
CREATE TABLE Messages
(
	MessID							INT IDENTITY(1, 1) PRIMARY KEY,
	Contents						NVARCHAR(200) NULL,
	DateSend						DATETIME NOT NULL DEFAULT GETDATE(),
	MesStatus						BIT NOT NULL DEFAULT 0,
	FromUserID						INT NOT NULL,
	ToUserID						INT NOT NULL,
	CONSTRAINT FK_Messages_UsersFrom_ID FOREIGN KEY (FromUserID) REFERENCES Users(UserID),
	CONSTRAINT FK_Messages_UsersTo_ID FOREIGN KEY (ToUserID) REFERENCES Users(UserID)
)
GO


-- Friends --------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Friends') DROP TABLE Friends
GO
CREATE TABLE Friends
(
	FromUserID						INT NOT NULL,
	ToUserID						INT NOT NULL,
	Relationship					NVARCHAR(100) NULL,
	DateFriends						DATETIME NULL,
	FriStatus						BIT NOT NULL DEFAULT 0,
	CONSTRAINT PK_Messages PRIMARY KEY(FromUserID, ToUserID),
	CONSTRAINT FK_Messages_UsersFromFe_ID FOREIGN KEY (FromUserID) REFERENCES Users(UserID),
	CONSTRAINT FK_Messages_UsersToFe_ID FOREIGN KEY (ToUserID) REFERENCES Users(UserID)
)
GO


-- Posts --------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Posts') DROP TABLE Posts
GO
CREATE TABLE Posts
(
	PosID							INT IDENTITY(1, 1) PRIMARY KEY,
	Contents						NTEXT NOT NULL,
	DateCreate						DATETIME NOT NULL DEFAULT GETDATE(),
	UserID							INT NOT NULL,
	CONSTRAINT FK_Posts_Users_ID	FOREIGN KEY (UserID) REFERENCES Users(UserID)
)
GO


-- Comments --------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Comments') DROP TABLE Comments
GO
CREATE TABLE Comments
(
	ComID							INT IDENTITY(1, 1) PRIMARY KEY,
	Contents						NTEXT NOT NULL,
	DateCreate						DATETIME NOT NULL DEFAULT GETDATE(),
	UserID							INT NOT NULL,
	PosID							INT NOT NULL,
	CONSTRAINT FK_Comments_Users_ID	FOREIGN KEY (UserID) REFERENCES Users(UserID),
	CONSTRAINT FK_Comments_Posts_ID	FOREIGN KEY (PosID) REFERENCES Posts(PosID)
)
GO